﻿<%@ Page Language="C#" AutoEventWireup="true" CodeFile="r_comercial_mensalidades_por_faixa_de_valores.aspx.cs" Inherits="relatorios_r_comercial_mensalidade_por_valores" %>

<%    
    /*string Permissao_RelatoriosFinanceirosComercial = "" + this.Session["Permissao_RelatoriosFinanceirosComercial"];
    if (Permissao_RelatoriosFinanceirosComercial != "1")
    {
        this.Response.Redirect("../login.aspx");
    } */       
%>

<div style="background-color: White; min-height: 600px;">
    <%
        bool hasResults = false;
        DateTime now = DateTime.Now;
        string month = this.Request["month"];
        string year = this.Request["year"];            
        int iMonth = -1;
        int iYear = -1;            
        int maxDay = 31;
        double sum = 0;
        
        // Month
        if (string.IsNullOrEmpty(month) == true)
        {
            iMonth = now.Month;
        }
        else
        {
            try
            {
                iMonth = int.Parse(month);
            }
            catch { iMonth = now.Month; }
        }
        // Year            
        if (string.IsNullOrEmpty(year) == true)
        {
            iYear = now.Year;
        }
        else
        {
            try
            {
                iYear = int.Parse(year);
            }
            catch { iYear = now.Year; }                
        }

        maxDay = DateTime.DaysInMonth(iYear, iMonth);            
        //DateTime targetDate = DateTime.Now;     
    %>

    <%
        string min = "" + this.Request["min"];
        string max = "" + this.Request["max"];        
    %>
    <center>
        <form>
            <table>
                <tr>
                    <td>Mês</td>
                    <td>Ano</td>
                    <td></td>
                </tr>

                <tr>
                    <td>
                        <input type="text" id="month" name="month" value="<%= iMonth %>" />
                    </td>
                    <td><input type="text" id="year" name="year" value="<%= iYear %>" /></td>
                    <td></td>
                </tr>                

                <tr>
                    <td>mensalidade mínima</td>
                    <td>mensalidade máxima</td>
                    <td></td>
                </tr>

                <tr>
                    <td>
                        <input type="text" id="min" name="min" value="<%= min %>" />
                    </td>
                    <td><input type="text" id="max" name="max" value="<%= max %>" /></td>
                    <td><input type="submit" value="ok" /></td>
                </tr>
            </table>
        </form>

        <style>
            .resultTableTd1
            {
                width: 80px;
            }
            
            .resultTableTd2
            {
                width: 300px;
            }
            
            .resultTableTd3
            {
                width: 300px;
            }                        
        </style>

        <br />

        <table class="resultTable">
            <tr>
                <td class="resultTableTd1"><strong>Valor</strong></td>
                <td class="resultTableTd2"><strong>Vendedor</strong></td>
            </tr>

            <%
                hasResults = false;
                PL.DataBase.DAO.Instance.Setup(PL.DataBase.DatabaseTypes.SqlServer, PL.General.Instance.BIConnectionString);
                PL.DataBase.DAO.Instance.Connector.Open();

                string message = "";
                try
                {
                    DateTime targetDate = new DateTime(iYear, iMonth, 1);
                    DateTime maxDate = new DateTime(iYear, iMonth, maxDay);

                    string sql = "";
                    /*
                    sql += " SELECT        D0S002Solução, D0S001Vendedor, D2N003Cliente, SUM(F0S203Mensalidade) AS Total ";
                    sql += " FROM            BI_CP_Orcamentos ";
                    sql += " WHERE        ";
                    sql += " D2N009Data >= '" + targetDate.Day + "/" + targetDate.Month + "/" + targetDate.Year + "' ";
                    sql += " AND D2N009Data <= '" + maxDate.Day + "/" + maxDate.Month + "/" + maxDate.Year + "' ";
                    sql += " AND (D2N010Situacao = '3') ";
                    sql += " AND (F0S203Mensalidade IS NOT NULL) ";
                    if (string.IsNullOrEmpty(min) == false)
                    {
                        sql += " AND (F0S203Mensalidade >= " + min + ")";
                    }
                    if (string.IsNullOrEmpty(max) == false)
                    {
                        sql += " AND (F0S203Mensalidade <= " + max + ")";
                    }                    
                    sql += " GROUP BY D0S002Solução, D0S001Vendedor, D2N003Cliente";                    
                    */

                    sql += " SELECT * FROM (";
                    sql += "SELECT        TOP (100) PERCENT sistema.Usuario AS Vendedor, SUM(orcamentoitens.Valor * orcamentoitens.Qtde) AS Valor, SUM(orcamentoitens.Mensalidade) AS Mensalidade ";
                    sql += "FROM            orcamento LEFT OUTER JOIN ";
                    sql += "                         product LEFT OUTER JOIN ";
                    sql += "                         orcamentoitens ON product.Código = orcamentoitens.Cod_Produto ON orcamento.Codigo = orcamentoitens.CodOrcamento LEFT OUTER JOIN ";
                    sql += "                         sistema ON orcamento.CodVendedor = sistema.Codigo ";
                    sql += "WHERE        (orcamento.DTFinalizado >= CONVERT(DATETIME, '2011-12-01 00:00:00', 102)) AND (orcamento.CodCliente <> 1399) AND (CAST(orcamento.Data AS DATETIME)  ";
                    sql += "                         >= '" + targetDate.Day + "/" + targetDate.Month + "/" + targetDate.Year + "') AND (CAST(orcamento.Data AS DATETIME) <= '" + maxDate.Day + "/" + maxDate.Month + "/" + maxDate.Year + "') AND (orcamento.Situacao = 3)  ";
                                        
                    sql += " GROUP BY sistema.Usuario, orcamento.Situacao  ";
                    sql += " ORDER BY Vendedor) ";
                    sql += " as newtb WHERE 1=1 ";
                    if (string.IsNullOrEmpty(min) == false)
                    {
                        sql += " AND Mensalidade >= " + min + "";
                    }
                    if (string.IsNullOrEmpty(max) == false)
                    {
                        sql += " AND Mensalidade <= " + max + "";
                    }
                                        
                    //Response.Write(sql);                
                    var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
                    while (reader.Read() == true)
                    {
                        string total = "" + reader["Mensalidade"];
                        if (total != "0,00")
                        {
                            hasResults = true;
                            string sSum = "" + reader["Mensalidade"];
                            try
                            {
                                sum += double.Parse(sSum);
                            }
                            catch { }

                            double dTotal = 0;
                            try
                            {
                                dTotal = double.Parse(total);
                            }
                            catch { }                                                                                              
            %>
                            <tr>
                                <td class="resultTableTd1">R$ <%= dTotal.ToString("#,##0.00") %></td>
                                <td class="resultTableTd2"><%= reader["Vendedor"]%></td>
                            </tr>                
            <%                                                
                        }
                    }
                    reader.Close();
                }
                catch (Exception e)
                {
                    message = e.Message;
                }

                PL.DataBase.DAO.Instance.Connector.Close();
                if (hasResults == false)
                {
            %>
                    <td>Nenhum dado encontrado!</td>
                    <td><% this.Response.Write(message);  %></td>
            <%
                }
                else
                {
            %>
                    <td><br /><strong>TOTAL:</strong> R$ <%= sum.ToString("#,##0.00")%></td>
                    <td></td>                   
            <%
                }                                         
            %>
        </table>
    </center>
</div>